package com.oa.modules.approvalsec1.controller;

import com.oa.common.utils.PageUtils;
import com.oa.common.utils.R;
import com.oa.modules.approvalsec1.entity.Storagelog;
import com.oa.modules.approvalsec1.service.StoragelogService;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * (Storagelog)表控制层
 *
 * @author makejava
 * @since 2020-11-19 10:54:58
 */
@RestController
@RequestMapping("storagelog")
public class StoragelogController {
    /**
     * 服务对象
     */
    @Resource
    private StoragelogService storagelogService;

    /**
     * 通过主键查询单条数据
     *
     * @param id 主键
     * @return 单条数据
     */
    @GetMapping("selectOne")
    public Storagelog selectOne(Long id) {
        return this.storagelogService.queryById(id);
    }

    /**
     * 分页查询
     * @param params
     * @return
     */
    @PostMapping(value = "/queryPage")
    @RequiresPermissions("approvalSec1:storagelog:list")
    public R queryPage(@RequestParam Map<String, Object> params){
        PageUtils page = storagelogService.queryPage(params);
        return R.ok().put("page", page);
    }

    @PostMapping(value = "/upload")
    public R upload(@RequestParam("file") MultipartFile file) throws Exception {
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        List<Storagelog> list = new ArrayList<>();
        try{
            InputStream is = file.getInputStream();

            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(is));
            HSSFSheet sheet = workbook.getSheetAt(0);
            //获取多少行
            int rows = sheet.getPhysicalNumberOfRows();
            Storagelog storagelog = null;
            for (int j = 1; j < rows; j++) {
                storagelog = new Storagelog();
                //获得第 j 行
                HSSFRow row = sheet.getRow(j);
                storagelog.setOperation(getCellValueByCell(row.getCell(0)));
                storagelog.setProjectName(getCellValueByCell(row.getCell(1)));
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                storagelog.setCreateDate(df.parse(getCellValueByCell(row.getCell(2))));
                list.add(storagelog);
            }
            if(list!=null && list.size()>0){
                storagelogService.insertList(list);
            }
            return R.ok();
        }catch (Exception e) {
            return R.error();
        }
    }

    //获取单元格各类型值，返回字符串类型
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType=cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue()+"";;
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }
    @PostMapping("/downloadExcel")
    public void downloadExcel(HttpServletResponse response,HttpServletRequest request) {
        //方法一：直接下载路径下的文件模板
        try {
            //获取要下载的模板名称
            String fileName = "storageLogTemplate.xls";
            //设置要下载的文件的名称
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
            //通知客服文件的MIME类型
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            //获取文件的路径
            //String filePath = getClass().getResource("/static/template/" + fileName).getPath();
            String filePath = "/home/templates/" + fileName;
            FileInputStream input = new FileInputStream(filePath);
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {
                out.write(b, 0, len);
            }
            //修正 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"
            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
            input.close();
            //return Response.ok("应用导入模板下载完成");
        } catch (Exception ex) {
            //logger.error("getApplicationTemplate :", ex);
            //return Response.ok("应用导入模板下载失败！");
        }
    }
}
